﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using System.Data.OleDb;
using ZG_member.BLL;

namespace ZG_member
{
    public partial class ZhengZaiDaoChu : Form
    {
        public ZhengZaiDaoChu(int i,MainForm s)
        {
            InitializeComponent();
            ii = i;
            skinEngine1.SkinFile = Application.StartupPath + ConfigurationManager.AppSettings["Skin"];
            main=s;
        }
        public static int max = 0;
         int dangqian = 0;
         int ii = 0;
         MainForm main;
         public void TransferData(string excelFile, string sheetName, string connectionString)
         {

             //ZG_member.BLL.memberInfo adv = new ZG_member.BLL.memberInfo();
             //ZG_member.Model.memberInfo advMode = new ZG_member.Model.memberInfo();
             //DataTable advTB = adv.GetList("").Tables[0];

             DataSet ds = new DataSet();


             try
             {
                 try
                 {
                     //获取全部数据   
                     string strConn = "Provider=Microsoft.ACE.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended properties=\"Excel 12.0;Imex=1;HDR=Yes;\"";
                     OleDbConnection conn = new OleDbConnection(strConn);
                     conn.Open();
                     string strExcel = "";
                     OleDbDataAdapter myCommand = null;
                     strExcel = string.Format("select * from [{0}$]", sheetName);
                     myCommand = new OleDbDataAdapter(strExcel, strConn);
                     myCommand.Fill(ds, sheetName);
                     bool bl = true;
                     progressBar1.Maximum = ds.Tables[0].Rows.Count;
                     //如果目标表不存在则创建   
                     for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                     {
                         string strSql = "insert into memberInfo (AreasID,memberName,sex,Tel,createDate,expiresDate,Address,Email,tuiguangren,memo,birthDay,MemberType,consume,cardType,status,memberId) values('";
                         strSql += ds.Tables[0].Rows[i]["手机归属地"].ToString() + "','";
                         strSql += ds.Tables[0].Rows[i]["姓名"].ToString() + "','";
                         strSql += ds.Tables[0].Rows[i]["性别"].ToString() + "','";
                         strSql += ds.Tables[0].Rows[i]["电话"].ToString() + "','";
                         strSql += DateTime.Now.ToString("yyyy-MM-dd") + "','";
                         strSql += DateTime.Now.AddYears(1).ToString("yyyy-MM-dd") + "','";
                         strSql += ds.Tables[0].Rows[i]["地址"].ToString() + "','";
                         strSql += ds.Tables[0].Rows[i]["电子邮件"].ToString() + "','";
                         strSql += ds.Tables[0].Rows[i]["接待人"].ToString() + "','";
                         strSql += ds.Tables[0].Rows[i]["购买日期"].ToString() + ":" + ds.Tables[0].Rows[i]["购买明细"].ToString() + "','";
                         strSql += ds.Tables[0].Rows[i]["生日"].ToString() + "','";
                         strSql += ds.Tables[0].Rows[i]["用途"].ToString() + "',";
                         strSql += ds.Tables[0].Rows[i]["购买金额"].ToString() + ",";

                         //strSql += string.Format("{0} varchar(255),", c.ColumnName);

                         //if (advTB.Columns[i + 1].ToString() == ds.Tables[0].Columns[i].ToString())
                         //{
                         //for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                         //{

                         //    if (j == 0)
                         //    {
                         //        continue;
                         //    }

                         //    //if (j == 3)
                         //    //{
                         //    //    string sex = ds.Tables[0].Rows[i][j].ToString();
                         //    //    if (sex == "男")
                         //    //    {
                         //    //        ds.Tables[0].Rows[i][j] = true;
                         //    //    }
                         //    //    else
                         //    //    {
                         //    //        ds.Tables[0].Rows[i][j] = false;
                         //    //    }
                         //    //}
                         //    if (j == 5)
                         //    {
                         //        strSql += "'" + DateTime.Now.ToString("yyyy-MM-dd") + "',";
                         //        continue;
                         //    }
                         //    if (j == 6)
                         //    {
                         //        strSql += "'" + DateTime.Now.AddMonths(2).ToString("yyyy-MM-dd") + "',";
                         //        continue;
                         //    }

                         //    //if (j == ds.Tables[0].Rows.Count - 1)
                         //    //{

                         //    //    break;
                         //    //}
                         //    strSql += "'" + ds.Tables[0].Rows[i][j].ToString() + "',";

                         //}
                         //}
                         //else
                         //{
                         //    bl = false;
                         //    if (bl == false)
                         //    {
                         //        MessageBox.Show("Excel与数据库标题不一致");
                         //        return;
                         //    }
                         //    break;
                         //}
                         ZG_member.BLL.memberInfo user = new ZG_member.BLL.memberInfo();
                         DataTable dt = user.GetListSql("select memberId from memberInfo where id in (select MAX(id)from memberInfo)").Tables[0];
                         string memberId = dt.Rows[0][0].ToString();
                         int memID = Convert.ToInt32(memberId);
                         memID = memID + 1;
                         memberId = memID.ToString().PadLeft(7, '0');

                         strSql += "'金卡','停用','" + memberId + "',";
                         strSql = strSql.Trim(',') + ")";


                         using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
                         {
                             sqlconn.Open();
                             System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
                             command.CommandText = strSql;
                             command.ExecuteNonQuery();
                             sqlconn.Close();
                             this.progressBar1.Value++;

                         }

                     }
                     // 用bcp导入数据   
                     //using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
                     //{
                     //    bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
                     //    bcp.BatchSize = 100;//每次传输的行数   
                     //    bcp.NotifyAfter = 100;//进度提示的行数   
                     //    bcp.DestinationTableName = "goods";//目标表   
                     //    bcp.WriteToServer(ds.Tables[0]);
                     //}
                     
                 }
                 catch (Exception ex)
                 {
                     string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFile + ";" + "Extended properties=\"Excel 12.0;Imex=1;HDR=Yes;\"";
                     OleDbConnection conn = new OleDbConnection(strConn);
                     conn.Open();
                     string strExcel = "";
                     OleDbDataAdapter myCommand = null;
                     strExcel = string.Format("select * from [{0}$]", sheetName);
                     myCommand = new OleDbDataAdapter(strExcel, strConn);
                     myCommand.Fill(ds, sheetName);
                     bool bl = true;
                     progressBar1.Maximum = ds.Tables[0].Rows.Count;
                     //如果目标表不存在则创建   
                     for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                     {
                         string strSql = "insert into memberInfo (AreasID,memberName,sex,Tel,createDate,expiresDate,Address,Email,tuiguangren,memo,birthDay,MemberType,consume,cardType,status,memberId) values('";
                         strSql += ds.Tables[0].Rows[i]["手机归属地"].ToString() + "','";
                         strSql += ds.Tables[0].Rows[i]["姓名"].ToString() + "','";
                         strSql += ds.Tables[0].Rows[i]["性别"].ToString() + "','";
                         strSql += ds.Tables[0].Rows[i]["电话"].ToString() + "','";
                         strSql += DateTime.Now.ToString("yyyy-MM-dd") + "','";
                         strSql += DateTime.Now.AddYears(1).ToString("yyyy-MM-dd") + "','";
                         strSql += ds.Tables[0].Rows[i]["地址"].ToString() + "','";
                         strSql += ds.Tables[0].Rows[i]["电子邮件"].ToString() + "','";
                         strSql += ds.Tables[0].Rows[i]["接待人"].ToString() + "','";
                         strSql += ds.Tables[0].Rows[i]["购买日期"].ToString() + ":" + ds.Tables[0].Rows[i]["购买明细"].ToString() + "','";
                         strSql += ds.Tables[0].Rows[i]["生日"].ToString() + "','";
                         strSql += ds.Tables[0].Rows[i]["用途"].ToString() + "',";
                         strSql += ds.Tables[0].Rows[i]["购买金额"].ToString() + ",";

                         //System.Windows.Forms.MessageBox.Show(ex.Message);
                         //MessageBox.Show("请确保Excel格式内容正确!");
                         //return;
                         ZG_member.BLL.memberInfo user = new ZG_member.BLL.memberInfo();
                         DataTable dt = user.GetListSql("select memberId from memberInfo where id in (select MAX(id)from memberInfo)").Tables[0];
                         string memberId = dt.Rows[0][0].ToString();
                         int memID = Convert.ToInt32(memberId);
                         memID = memID + 1;
                         memberId = memID.ToString().PadLeft(7, '0');

                         strSql += "'金卡','停用','" + memberId + "',";
                         strSql = strSql.Trim(',') + ")";


                         using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
                         {
                             sqlconn.Open();
                             System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
                             command.CommandText = strSql;
                             command.ExecuteNonQuery();
                             sqlconn.Close();
                             this.progressBar1.Value++;

                         }

                     }
                     // 用bcp导入数据   
                     //using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
                     //{
                     //    bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
                     //    bcp.BatchSize = 100;//每次传输的行数   
                     //    bcp.NotifyAfter = 100;//进度提示的行数   
                     //    bcp.DestinationTableName = "goods";//目标表   
                     //    bcp.WriteToServer(ds.Tables[0]);
                     //}
                     
                 }
                 ZG_member.BLL.goods good = new ZG_member.BLL.goods();
                 DataTable dtt = good.GetList("").Tables[0];
                 MessageBox.Show("导入成功");
             }
             catch (Exception)
             {
                 MessageBox.Show("请确保Excel格式内容正确!");
                 return;
             }
            


         }
        private void ZhengZaiDaoChu_Load(object sender, EventArgs e)
        {

            this.timer1.Start();
            
        }
        public void ExportExcel(string fileName, DataGridView myDGV)
        {
            string saveFileName = "";
            //bool fileSaved = false;
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xls";
            saveDialog.Filter = "Excel文件|*.xls";
            saveDialog.FileName = fileName;
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0) return; //被点了取消 
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Office.Excel组件");
                return;
            }
            this.progressBar1.Maximum = Convert.ToInt32(main.deyeshu);
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 
            int colIndex = 0;
            //写入标题
            for (int i = 0; i < myDGV.ColumnCount; i++)
            {
                if (myDGV.Columns[i].Visible)
                {
                    colIndex++;
                    worksheet.Cells[1, colIndex] = myDGV.Columns[i].HeaderText;
                    //  worksheet.Cells[1, i] = myDGV.Columns[i].HeaderText;
                }
            }
            //写入数值
            int rowsCount = 0;
            for (int yeshu = 1; yeshu <= Convert.ToInt32(main.deyeshu); yeshu++)
            {
                try
                {

                    this.progressBar1.Value++;
                    memberInfo memInfo = new memberInfo();
                    DataTable dt = memInfo.Mains(main.size, yeshu.ToString(), "","").Tables[0];

                    myDGV.DataSource = dt;
                    consumeSolution consume = new consumeSolution();
                    Model.consumeSolution consumeMod = new Model.consumeSolution();
                    Areas area = new Areas();
                    ZG_member.Model.Areas areMode = new ZG_member.Model.Areas();
                    foreach (DataGridViewRow dgvr in myDGV.Rows)
                    {


                        if (dgvr.Cells["cardType1"].Value != null)
                        {
                            int ID = int.Parse(dgvr.Cells["cardType1"].Value.ToString());
                            consumeMod = consume.GetModelList("id='" + ID + "'")[0];
                            dgvr.Cells["NewcardType"].Value = consumeMod.solutionName;
                        }

                        if (dgvr.Cells["AreasID"].Value != null)
                        {
                            int id = int.Parse(dgvr.Cells["AreasID"].Value.ToString());
                            areMode = area.getModel(id);
                            dgvr.Cells["AreaName"].Value = areMode.AreaName;
                        }

                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }

                for (int r = 0; r < myDGV.Rows.Count; r++)
                {
                    colIndex = 0;

                    for (int i = 0; i < myDGV.ColumnCount; i++)
                    {
                        if (myDGV.Columns[i].Visible)
                        {
                            colIndex++;
                            if (colIndex == 3)
                            {
                                worksheet.Cells[rowsCount + 2, colIndex] = "'" + myDGV.Rows[r].Cells[i].Value;
                                continue;
                            }
                            if (colIndex == 6)
                            {
                                worksheet.Cells[rowsCount + 2, colIndex] = "'" + myDGV.Rows[r].Cells[i].Value;
                                continue;
                            }
                            if (colIndex == 9)
                            {
                                worksheet.Cells[rowsCount + 2, colIndex] = "'" + myDGV.Rows[r].Cells[i].Value;
                                continue;
                            }
                            worksheet.Cells[rowsCount + 2, colIndex] = myDGV.Rows[r].Cells[i].Value;
                        }
                    }
                    rowsCount++;
                    //System.Windows.Forms.Application.DoEvents();
                }
            }
            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应

            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);
                    //fileSaved = true;
                }
                catch (Exception ex)
                {
                    //fileSaved = false;
                    MessageBox.Show("导出文件时出错,文件可能正被打开！/n" + ex.Message);
                }

            }
            xlApp.Quit();
            GC.Collect();//强行销毁 
            // if (fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL

            MessageBox.Show("导出成功", "系统提示", MessageBoxButtons.OK);
            this.Close();
        }
        private void timer1_Tick(object sender, EventArgs e)
        {
            this.timer1.Stop();
            if (ii == 0)
            {
                this.label1.Text = "正在导入";
                this.Text = "正在导入";
                string connString = ConfigurationManager.AppSettings["ConnectionString"];
                System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
                if (fd.ShowDialog() == DialogResult.OK)
                {
                    TransferData(fd.FileName, "sheet1", connString);
                }
                main.shifouxianshi(MainForm.Operator);
                main.CurrYe();
                this.Close();
            }
            else
            {
                ExportExcel("",main.dgvMemberInfo);
                this.Close();
            }
        }
    }
}
